package org.fao.pims.persistence;


import java.io.InputStream;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;
import java.sql.*;
import javax.sql.*;

import java.util.*;
import org.fao.pims.model.*;

public class AllCropDAOImpl implements AllCropDAO {
	
	private DataSource dataSource = null;
	
	public AllCropDAOImpl(DataSource ds){
		
		this.dataSource = ds;
		
	}
	
	public void getAllCrops(){
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
       
        
      
        try {
            
            conn = dataSource.getConnection();

            
            DatabaseMetaData dbmd = conn.getMetaData();
            
            
            int transactionIsolation = dbmd.getDefaultTransactionIsolation();
            String dbProductName = dbmd.getDatabaseProductName();
            String dbVersion = dbmd.getDatabaseProductVersion();
            String driverName = dbmd.getDriverName();
            String url = dbmd.getURL();
            String info = "Connect to the db " + dbProductName + " " +
			            " DB Version: " + dbVersion + 
			            " Driver: " + driverName +
			            " URL: " + url +
			            " Transaction Isolation: " + transactionIsolation;
            
            System.out.println(info);
            
         
            
            
            String query = "SELECT * FROM  allpollcrops";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();
            
            
            
           
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                System.out.println("Resulset closed");
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
               
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                System.out.println("Prep Statement closed");
                
            }
            catch(Exception e) { 
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
              
            }
            
            try 
            { 
                if( conn != null )
                    conn.close(); 
                System.out.println("Connection closed");
                
            }
            catch(Exception e) {
                System.out.println("Class UserDAO, getAllUsers() method call error..");
                e.printStackTrace();
               
            }
            
            
            
        }
        
     

	}
	
	public List getCropByName(String name){
		
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
       
        
      
        try {
            
            conn = dataSource.getConnection();          
            
            String query = "SELECT * FROM  allpollcrops " +
            					"WHERE scientific_name LIKE'" + name + "%' " +
            					"OR common_name LIKE'" + name + "%'";
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            result = makeBeanList(resultsetToList(rset));
            
           
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
            
            try 
            { 
                if( rset != null )
                    rset.close(); 
                System.out.println("Resulset closed");
            }
            catch(Exception e) {
                
                e.printStackTrace();
               
            }
            
            try 
            { 
                if( pstmt != null )
                    pstmt.close(); 
                System.out.println("Prep Statement closed");
                
            }
            catch(Exception e) { 
                
                e.printStackTrace();
              
            }
            
            try 
            { 
                if( conn != null )
                    conn.close(); 
                System.out.println("Connection closed");
                
            }
            catch(Exception e) {
                
                e.printStackTrace();
               
            }
            
            
            
        }

		
		return result;
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    private List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            CropSpecie bean = new CropSpecie();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                
                if( columnName.equalsIgnoreCase("SCIENTIFIC_NAME") )
                {
                    bean.setScientificName( (String)obj );
                    //System.out.println("scientific name: " + bean.getScientificName());
                }
                if( columnName.equalsIgnoreCase("COMMON_NAME") )
                {
                    bean.setCommonName( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("HIGHER_GROUP") )
                {
                    bean.setHigherGroup( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("GENERAL_IMPACT") )
                {
                    bean.setGeneralImpact( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("SPECIFIC_IMPACT") )
                {
                    bean.setSpecificImpact( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("BREEDING_SYSTEM") )
                {
                    bean.setBreedingSystem( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("POLL_NO_VISITORS") )
                {
                    bean.setPollNoVisitors( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("POLLINATORS") )
                {
                    bean.setPollinators( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("FAO_COMMODITY_CODE") )
                {
                    bean.setFaoCommodityCode( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("SOURCE") )
                {
                    bean.setSource( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
               
                
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }

	
	/**
     * Copia i record del result set passato come argomento in un oggetto List. Per 
     * ogni record costruisce una lista contenente il nome del campo, il tipo di dato
     * e il valore.
     */
    protected List resultsetToList(ResultSet rs) throws SQLException
    {
        int rowsCount = 0;
        ResultSetMetaData rsmd = null;
        List row = new ArrayList();
        
        rsmd = rs.getMetaData();
        
        String tableName = rsmd.getTableName(1);
        
        int cols = rsmd.getColumnCount();
            
            while(rs.next()){
                
                rowsCount++;

                List fields = new ArrayList();
                for(int i = 0; i < cols; i++){
                    String columnClassName = rsmd.getColumnClassName(i + 1);
                    //System.out.print("User column data type: " + columnClassName + " ");
                    TableFieldHandler fieldhandler;
                    if( !columnClassName.equals("java.lang.Object") )
                    {
                        Object object = rs.getObject(i + 1);
                        String columnName = rsmd.getColumnName(i + 1);     
                       
                        //System.out.println("column name: " + columnName + " value: " + object.toString());
                        fieldhandler = new TableFieldHandler(columnClassName, object, columnName); 
                    }
                    else 
                    {
                        java.sql.Blob blob = rs.getBlob(i + 1);
                        
                        String columnName = rsmd.getColumnName(i + 1);  
                       
                        InputStream blobstream = blob.getBinaryStream();
                        fieldhandler = new TableFieldHandler(columnClassName,
                                                                               blobstream, 
                                                                               columnName);
                    }
                    
                    fields.add(fieldhandler);
                    
                }
                
                row.add(fields);
                
            }
            
        
        return row;
    }


}
